SQL Server – Removing String Prefix

Here's a totally different approach that seems to work for this case. Expand and customize it as needed. Change the value of @l to control the number of bits/questions in the output.

CREATE FUNCTION dbo.bin_val(@val int, @trim bit)
RETURNS varchar(max)
AS
BEGIN
    DECLARE @hval varchar(256) = REPLACE(CONVERT(varchar, CAST(@val AS varbinary), 1), '0x', '')
    DECLARE @bval varchar(256) = ''
    DECLARE @i int = 1
    WHILE @i <= LEN(@hval)
    BEGIN
        SET @bval = @bval + CASE SUBSTRING(@hval, @i, 1)
                WHEN '0' THEN '0000'
                WHEN '1' THEN '0001'
                WHEN '2' THEN '0010'
                WHEN '3' THEN '0011'
                WHEN '4' THEN '0100'
                WHEN '5' THEN '0101'
                WHEN '6' THEN '0110'
                WHEN '7' THEN '0111'
                WHEN '8' THEN '1000'
                WHEN '9' THEN '1001'
                WHEN 'A' THEN '1010'
                WHEN 'B' THEN '1011'
                WHEN 'C' THEN '1100'
                WHEN 'D' THEN '1101'
                WHEN 'E' THEN '1110'
                WHEN 'F' THEN '1111'
            END
        SET @i = @i + 1
    END

    IF @trim = 1
        SET @bval = RIGHT(@bval, LEN(@bval) - ISNULL(NULLIF(CHARINDEX('1', @bval), 0), LEN(@bval)) + 1)

    RETURN @bval
END

GO

DECLARE @l int = 8
SELECT
    number,
    RIGHT(REPLACE(REPLACE(dbo.bin_val(number, 0), '1', 'X'), '0', '-'), @l)
FROM master..spt_values
WHERE type = 'P'
    AND number <= POWER(2, @l) - 1

The only way to really backup a database is to create a backup of the database (using BACKUP DATABASE). All the other methods (scripting database schema and content, copying files around, OS file snapshots) are workarounds, not database backups.

So, as I see it, the best way to have a database copied from one place to another is to restore it from a backup (RESTORE DATABASE). There's no other effective and more efficient way as I see it. That's my 2 cents, anyway.

Now, in order to do all that preparation, I'd make different stored procedures (using dynamic sql to build proper names, paths..etc) that will:

• restore databases from a specific location;
• prepare specific permissions -> maybe different users..etc;
• prepare replication objects;

PS: if it's a possibility, make sure to enable backup compression, as it will really help with size and speed.

PS2: taking into considerations your backup file sizes I wouldn't worry too much about the duration of the operation. Should be fast even on a usual developer box.